let express = require('express');
const { insertSql, updateSql, deleteSql } = require('../../utils');
let router = express.Router();
let connection = require('../dataBase');
const Result = require('../result');

// 添加一篇文章
router.post('/add', function (req, res) {
  const sql = insertSql('article', req.body);
  connection.query(sql, (err, data) => {
    if (err) {
      return new Result(res, 1200).fail();
    }
    return new Result(res, 200, data, '添加成功').success();
  });
});

// 获取最新的num条文章
router.get('/public/getTopN', function (req, res) {
  const sql = `SELECT  * from article ORDER BY time DESC LIMIT 0,${req.query.num}`;
  connection.query(sql, (err, data) => {
    if (err) {
      return new Result(res, 1200).fail();
    }
    return new Result(res, 200, data, '添加成功').success();
  });
});

// 获取最新的num条文章
router.get('/public/getTopNNews', function (req, res) {
  const sql = `SELECT  * from article where isNews=1 ORDER BY time DESC LIMIT 0,${req.query.num}`;
  connection.query(sql, (err, data) => {
    if (err) {
      return new Result(res, 1200).fail();
    }
    return new Result(res, 200, data, '添加成功').success();
  });
});

// 获取最新的num条文章(附带浏览量)
router.get('/public/getTopNArticle', function (req, res) {
  // const sql = `SELECT  * from article where isNews=1 ORDER BY time DESC LIMIT 0,${req.query.num}`;
  const sql = `
  SELECT 
    art.*,
    li.likeNum,
    li.starNum,
    com.commentNum,
    li.lookNum
  FROM
    article art
    LEFT JOIN ( SELECT articleId, sum( action.islike ) likeNum, sum( action.star ) starNum, sum( action.look ) lookNum FROM action GROUP BY articleId ) li ON art.id = li.articleId
    LEFT JOIN ( SELECT articleId, count( * ) commentNum FROM ${'`comment`'} WHERE ${'`level`'} = 0 GROUP BY articleId ) com ON art.id = com.articleId 
  ORDER BY
    time DESC
  LIMIT 0,${req.query.num}
  `;
  connection.query(sql, (err, data) => {
    if (err) {
      return new Result(res, 1200).fail();
    }
    return new Result(res, 200, data, '添加成功').success();
  });
});

// 获取相关推荐 type 0资讯 1问答 2普通
router.get('/public/getAbout', function (req, res) {
  const arr = ['isNews=1', 'isQuestion=1', 'isQuestion=0 and isNews=0'];
  const sql = `SELECT  * from article where ${
    arr[req.query.type * 1]
  } order by time DESC limit 0,10`;
  connection.query(sql, (err, data) => {
    if (err) {
      return new Result(res, 1200).fail();
    }
    return new Result(res, 200, data, '添加成功').success();
  });
});

// 获取所有文章
router.get('/getAll', function (req, res) {
  const { accountId } = req.query;
  const sql = `
  SELECT 
    art.*,
    li.likeNum,
    li.starNum,
    com.commentNum,
    li.lookNum
  FROM
    article art
    LEFT JOIN ( SELECT articleId, sum( action.islike ) likeNum, sum( action.star ) starNum, sum( action.look ) lookNum FROM action GROUP BY articleId ) li ON art.id = li.articleId
    LEFT JOIN ( SELECT articleId, count( * ) commentNum FROM ${'`comment`'} WHERE ${'`level`'} = 0 GROUP BY articleId ) com ON art.id = com.articleId 
  WHERE
    accountId = ${accountId}
  ORDER BY
    time DESC
  `;
  connection.query(sql, (err, data) => {
    if (err) {
      return new Result(res, 1200).fail();
    }
    return new Result(res, 200, data, '获取成功').success();
  });
});

// 获取所有咨询
router.get('/public/getAllNews', function (req, res) {
  const sql = `
  SELECT
    * 
  FROM
    article 
  WHERE
    isNews = 1 and 
    (
    tags LIKE '%粮油%' 
    OR tags LIKE '%果蔬及花丼%' 
    OR tags LIKE '%林产品%' 
    OR tags LIKE '%畜禽产品%' 
    OR tags LIKE '%水产品和其他农副产品%'
    )
  `;
  connection.query(sql, (err, data) => {
    if (err) {
      return new Result(res, 1200).fail();
    }
    return new Result(res, 200, data, '获取成功').success();
  });
});

// 获取所有提问
router.get('/public/getAllQuestion', function (req, res) {
  const sql = `
  SELECT 
    art.*,
    li.likeNum,
    li.starNum,
    com.commentNum,
    li.lookNum
  FROM
    ( SELECT article.*,acc.nickName from article,account acc WHERE acc.id=article.accountId) art
    LEFT JOIN ( SELECT articleId, sum( action.islike ) likeNum, sum( action.star ) starNum, sum( action.look ) lookNum FROM action GROUP BY articleId ) li ON art.id = li.articleId
    LEFT JOIN ( SELECT articleId, count( * ) commentNum FROM ${'`comment`'} WHERE ${'`level`'} = 0 GROUP BY articleId ) com ON art.id = com.articleId 
  WHERE
    isQuestion = 1
  ORDER BY
    time DESC
  `;
  connection.query(sql, (err, data) => {
    if (err) {
      return new Result(res, 1200).fail();
    }
    return new Result(res, 200, data, '获取成功').success();
  });
});

// 获取一篇文章
router.get('/public/getOne', function (req, res) {
  const { id } = req.query;
  // const sql = `SELECT article.*,acc.nickName from article,account acc WHERE article.id=${id} and acc.id=article.accountId`;
  const sql = `
  SELECT 
  art.*,
  li.likeNum,
  li.starNum,
  com.commentNum,
  li.lookNum
FROM
  ( SELECT article.*,acc.nickName from article,account acc WHERE acc.id=article.accountId) art
  LEFT JOIN ( SELECT articleId, sum( action.islike ) likeNum, sum( action.star ) starNum, sum( action.look ) lookNum FROM action GROUP BY articleId ) li ON art.id = li.articleId
  LEFT JOIN ( SELECT articleId, count( * ) commentNum FROM ${'`comment`'} WHERE ${'`level`'} = 0 GROUP BY articleId ) com ON art.id = com.articleId 
WHERE
  art.id = ${id}
ORDER BY
  time DESC
  `;
  connection.query(sql, (err, data) => {
    if (err) {
      return new Result(res, 1200).fail();
    }
    return new Result(res, 200, data[0], '获取成功').success();
  });
});

router.post('/update', function (req, res) {
  const sql = updateSql('article', req.body.id, req.body);
  connection.query(sql, (err, data) => {
    if (err) {
      return new Result(res, 1200).fail();
    }
    return new Result(res, 200, data, '添加成功').success();
  });
});

router.post('/delete', function (req, res) {
  const sql = deleteSql('article', req.query.id);
  connection.query(sql, (err, data) => {
    if (err) {
      return new Result(res, 1200).fail();
    }
    return new Result(res, 200, data, '添加成功').success();
  });
});

router.get('/public/search', function (req, res) {
  const key = req.query.key;
  const sql = `
  SELECT 
    art.*,
    li.likeNum,
    li.starNum,
    com.commentNum,
    li.lookNum
  FROM
    article art
    LEFT JOIN ( SELECT articleId, sum( action.islike ) likeNum, sum( action.star ) starNum, sum( action.look ) lookNum FROM action GROUP BY articleId ) li ON art.id = li.articleId
    LEFT JOIN ( SELECT articleId, count( * ) commentNum FROM ${'`comment`'} WHERE ${'`level`'} = 0 GROUP BY articleId ) com ON art.id = com.articleId 
  WHERE
    art.title LIKE '%${key}%' 
    OR art.introduction LIKE '%${key}%' 
    OR art.content LIKE '%${key}%'
    OR art.tags LIKE '%${key}%'
  ORDER BY
    time DESC
  `;
  connection.query(sql, (err, data) => {
    if (err) {
      return new Result(res, 1200).fail();
    }
    return new Result(res, 200, data, '添加成功').success();
  });
});

module.exports = router;
